

** make a table of bounds

/*


load and collapse to weekly sums
save as tempory files
stitch together 


*/


********************************************************************************
********************************************************************************
** load and save all tests
********************************************************************************
********************************************************************************

** all tests

use health/test_daily_panel, clear
merge m:1 sid using health/demographics, keep(1 3) assert(2 3) ///
	nogen keepusing(sid age_wide)

	** aggregate 
	egen week = cut(date), at(`=mdy(3,13,2020)'(7)`=mdy(12,22,2020)')			
	drop if missing(week) 
	drop if missing(age_wide) | age_wide == 0
	
	** tested/positive at least once in this week?
	collapse (max) positive tested, by(week age_wide sid)	
	collapse (sum) tested positive, by(week age_wide)
	
	merge m:1 age_wide using data/age_wide_counts, assert(3) nogen

	gen test_rate = tested / n
	gen conf_pos_rate = positive / n
	gen pos_rate = positive / tested

	list age_wide n test_rate conf_pos_rate pos_rate if week == mdy(3,27,2020)
	
	
	collapse (mean) test_rate  	(rawsum) n  		[fw=n], by(week)
	

	list if week == mdy(3,27,2020)

	format week %td
	list, noobs clean
	
	gen group ="All"
	save health/all_weekly_wtd, replace 
	


********************************************************************************
********************************************************************************
** load and save the hospital tests, by group
********************************************************************************
********************************************************************************
foreach group in  no_icli any_major any_icli pool_icli {
	
	
	* load the data
	use health/inp_clean if ~no_dx, clear
	if "`group'" == "no_icli" gen no_icli = any_icli == 0
	if "`group'" == "pool_icli" gen pool_icli = 1
	keep if `group' == 1

	merge m:1 sid using health/demographics, assert(2 3) keep(1 3) ///
		keepusing(sid age_wide) nogen
	drop if missing(age_wide) | age_wide == 0
	egen week = cut(admit_date), at(`=mdy(3,13,2020)'(7)`=mdy(12,22,2020)')		
	
	drop if missing(week)
	collapse (sum) test_tight (count) n=test_tight, ///
		by(week age_wide)
	gen test_rate = test_tight / n
	
	rename n n_hospitalizations 
	merge m:1 age_wide using data/age_wide_counts, assert(3)
	rename n n_age_group
	desc, full 
	
	

	collapse (mean) test_rate (rawsum) n_hospitalizations [fw=n_age_group], by(week)
	rename n_hospitalizations n
	
	gen group = "`group'"
	save health/`group'_weekly_wtd, replace

	
}

********************************************************************************
********************************************************************************
** append together etc
********************************************************************************
********************************************************************************

clear
foreach group in all any_icli no_icli any_major pool_icli {
	append using health/`group'_weekly_wtd
}



gen groupn = 1 if group == "All"
replace groupn = 2 if group == "no_icli"
replace groupn = 3 if group == "any_major"
replace groupn = 4 if group == "any_icli"
replace groupn = 5 if group == "pool_icli"

reshape wide test_rate n group, i(week) j(groupn) 

gen month = month(week)
gen day = day(week)
gen year = year(week)
tostring month day year, replace
replace day = "0" + day if length(day)==1
gen date_str = month + "/" + day + "/" + year  + " & " 

forvalues g = 1/5 {
	gen s = string(test_rate`g', "%4.3f") + " & " 
	drop test_rate`g'
	rename s test_rate`g'
	gen s = string(n`g', "%12.0fc") + " & "
	drop n`g'
	rename s n`g'
}
replace test_rate4 = subinstr(test_rate4, "&", "\\",.)

drop if missing(date)

gen weeks = string(week, "%td") 
replace weeks  = subinstr(weeks, "2020", "", .) + " & "

outsheet weeks test_rate1 n2 test_rate2 n3 test_rate3 n4 test_rate4 ///
	using tables/test_rate_by_group_wtd.tex, replace noquote nonames delimit(" ")
	
	
clear
foreach group in all any_icli no_icli any_major {
	append using health/`group'_weekly_wtd
}
	
# delimit ;
twoway
	(line test_rate week if group == "All", color(navy) )
	(pci .03 `=mdy(12,4,2020)' .08 `=mdy(12,4,2020)', color(navy))
	
	(line test_rate week if group == "no_icli", color(maroon) )
	(pci .38 `=mdy(10,1,2020)' .33 `=mdy(10,1, 2020)', color(maroon))
	
	(line test_rate week if group == "any_major", color(maroon) lpattern(dash) )
	(pci .20 `=mdy(10,1,2020)' .25 `=mdy(10,1, 2020)', color(maroon))
	
	(line test_rate week if group == "any_icli", color(gs5))	
	(pci .74 `=mdy(9,13,2020)' .66 `=mdy(9,13,2020)', color(gs5))
	,
	xsize(9) ysize(6)
	ylabel(0(.2).8)
	legend(off)
	xtitle("")
	ytitle("")
	xlabel(`=mdy(3,13,2020)' "3/13"	`=mdy(4,13,2020)' "4/13" 
		`=mdy(5,13,2020)' "5/13" `=mdy(6,13,2020)' "6/13" `=mdy(7,13,2020)' "7/13"
		`=mdy(8,13,2020)' "8/13" `=mdy(9,13,2020)' "9/13" `=mdy(10,13,2020)' "10/13"
		`=mdy(11,13,2020)' "11/13" `=mdy(12,13,2020)' "12/13")
	graphregion(color(white))
	text(.76 `=mdy(8,22,2020)' "ICLI hospitalizations", size(small) 
		place(e) color(gs5))
	
	text(.41 `=mdy(10,1,2020)' "Non-ICLI hospitalizations", size(small)
		 color(maroon))	
	text(.18 `=mdy(10,1,2020)' "Clear cause hospitalizations", size(small) 
		color(maroon))
	
	text(.1 `=mdy(12,15,2020)' "Population", size(small) place(w) color(navy))
;
# delimit cr
	
graph export figures/tests_all_groups_wtd.pdf, replace 
	
